package com.hele.sts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hele.sts.pub.ConnectDB;
import com.hele.sts.pub.SpringContextHolder;

public class QLogDao {
	static Logger logger = LoggerFactory.getLogger(QLogDao.class);
	public static final String TABLE = "da_q_log";

	public static boolean insert(QLog bean) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ConnectDB connsyb = null;

		sql = "INSERT INTO da_q_log (logid,userid,username_EK,yhlx,username,cxrq,nbxh,zch,qymc,realname,yhsf,bz,dyfs,cxmd,djid,days,sfdy) "
				+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		logger.debug(sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			pstmt.setString(i++, bean.getLogId());

			pstmt.setString(i++, bean.getUserId());

			pstmt.setString(i++, bean.getUserNameEk());

			pstmt.setString(i++, bean.getYhlx());

			pstmt.setString(i++, bean.getUserName());

			pstmt.setTimestamp(i++, new Timestamp(bean.getCxrq().getTime()));

			pstmt.setString(i++, bean.getNbxh());

			pstmt.setString(i++, bean.getZch());

			pstmt.setString(i++, bean.getQymc());

			pstmt.setString(i++, bean.getRealName());

			pstmt.setString(i++, bean.getYhsf());

			pstmt.setString(i++, bean.getBz());
			pstmt.setString(i++, bean.getDyfs());
			// xuning add 20150122
			pstmt.setString(i++, bean.getCxmd());
			pstmt.setString(i++, bean.getDjid());
			pstmt.setInt(i++, bean.getDays());
			pstmt.setString(i++, bean.getSfdy());

			pstmt.execute();
			return true;
		} catch (SQLException e) {
			logger.error("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}
		DbClose.freePreparedStatement(pstmt);
		DbClose.freeConnection(conn);

		return false;
	}

	public static List load(String userId, String userNameEk, String yhlx, String userName, String zch, String qymc,
			String yhsf, Date start, Date end, int page, int pageSize, String paixu, String djjg, String realName,String cxmd, String bz) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;

		//sql = "SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (select  a.*,(select nvl(sum(days),0) from da_q_printlog s where a.userid=s.userid and a.nbxh=s.nbxh and a.djid=s.djid) dyys from da_q_log a WHERE 1=1";
//		sql = "SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (select  t.*,(select nvl(sum(days),0) from da_q_printlog s "
//				+ "where t.userid=s.userid and t.nbxh=s.nbxh and t.djid=s.djid and trunc(t.cxrq,'dd')=trunc(s.dyrq,'dd')) dyys from "
//				+ "(select row_number()over(partition by userid,username,nbxh,trunc(cxrq,'dd') order by cxrq desc) as no,a.* from da_q_log a WHERE 1 = 1";
		sql = "SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (select  t.*,(select nvl(sum(days),0) from da_q_printlog s "
				+ "where t.userid=s.userid and t.nbxh=s.nbxh and t.djid=s.djid and dyrq >= ? and dyrq <= ? ) dyys from "
				+ "(select row_number()over(partition by userid,username,nbxh,djid order by cxrq desc) as no,a.* from da_q_log a WHERE 1 = 1";
		if ((djjg != null) && (!"".equals(djjg))) {
			sql = sql + " and exists (select 1 from da_q_ek_user b where a.userid=b.userid and b.unit='" + djjg + "') ";
		}
		if ((userId != null) && (userId.length() > 0)) {
			sql = sql + " AND userid LIKE ?";
		}
		if ((userNameEk != null) && (userNameEk.length() > 0)) {
			sql = sql + " AND username_EK LIKE ? ";
		}
		if ((yhlx != null) && (yhlx.length() > 0)) {
			sql = sql + " AND yhlx LIKE ? ";
		}
		if ((userName != null) && (userName.length() > 0)) {
			sql = sql + " AND username LIKE ? ";
		}
		if ((zch != null) && (zch.length() > 0)) {
			sql = sql + " AND zch LIKE ? ";
		}
		if ((qymc != null) && (qymc.length() > 0)) {
			sql = sql + " AND qymc LIKE ? ";
		}
		if ((yhsf != null) && (yhsf.length() > 0)) {
			sql = sql + " AND yhsf LIKE ? ";
		}
		if ((realName != null) && (realName.length() > 0)) {
			sql = sql + " AND realname LIKE ? ";
		}
		if ((cxmd != null) && (cxmd.length() > 0)) {
			sql = sql + " AND cxmd = ? ";
		}
		if (start != null) {
			sql = sql + " AND cxrq >= ? ";
		}
		if (end != null) {
			sql = sql + " AND cxrq <= ? ";
		}
		if ((bz != null) && (bz.length() > 0)) {
			sql = sql + " AND bz LIKE ? ";
		}
		
		sql = sql + ") t where t.no=1 ";
		if ((paixu != null) && (paixu.length() > 0)) {
			sql = sql + " order by " + paixu;
			if ("cxrq".equals(paixu)) {
				sql = sql + " desc";
			}
		}
		if (pageSize > 1) {
			sql = sql + ") row_ WHERE rownum <= ?*?) WHERE rownum_ > (?-1)*?";
		}
		logger.debug(sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if (start == null) {
				Calendar cd = Calendar.getInstance();
				cd.set(2000, 1, 1);
				start = cd.getTime(); 
			}
			pstmt.setTimestamp(i++, new Timestamp(start.getTime()));
			
			if (end != null) {
				Calendar cd = Calendar.getInstance();
				cd.setTime(end);
				cd.add(5, 1);
				pstmt.setTimestamp(i++, new Timestamp(cd.getTimeInMillis()));
			} else {
				Calendar cd = Calendar.getInstance();
				cd.add(5, 1);
				pstmt.setTimestamp(i++, new Timestamp(cd.getTimeInMillis()));
			}
			
			if ((userId != null) && (userId.length() > 0)) {
				pstmt.setString(i++, "%" + userId + "%");
			}
			if ((userNameEk != null) && (userNameEk.length() > 0)) {
				pstmt.setString(i++, "%" + userNameEk + "%");
			}
			if ((yhlx != null) && (yhlx.length() > 0)) {
				pstmt.setString(i++, "%" + yhlx + "%");
			}
			if ((userName != null) && (userName.length() > 0)) {
				pstmt.setString(i++, "%" + userName.toUpperCase() + "%");
			}
			if ((zch != null) && (zch.length() > 0)) {
				pstmt.setString(i++, "%" + zch + "%");
			}
			if ((qymc != null) && (qymc.length() > 0)) {
				pstmt.setString(i++, "%" + qymc + "%");
			}
			if ((yhsf != null) && (yhsf.length() > 0)) {
				pstmt.setString(i++, "%" + yhsf + "%");
			}
			if ((realName != null) && (realName.length() > 0)) {
				pstmt.setString(i++, "%" + realName + "%");
			}
			if ((cxmd != null) && (cxmd.length() > 0)) {
				pstmt.setString(i++, cxmd);
			}
			if (start != null) {
				pstmt.setTimestamp(i++, new Timestamp(start.getTime()));
			}
			if (end != null) {
				Calendar cd = Calendar.getInstance();
				cd.setTime(end);
				cd.add(5, 1);
				pstmt.setTimestamp(i++, new Timestamp(cd.getTimeInMillis()));
			}
			if ((bz != null) && (bz.length() > 0)) {
				pstmt.setString(i++, "%" + bz + "%");
			}
			if (pageSize > 1) {
				pstmt.setInt(i++, page);
				pstmt.setInt(i++, pageSize);
				pstmt.setInt(i++, page);
				pstmt.setInt(i++, pageSize);
			}
			rs = pstmt.executeQuery();
			List list = new ArrayList();
			while (rs.next()) {
				list.add(rsToBean1(rs));
			}
			List localList1 = list;
			return localList1;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}

		return null;
	}

	public static int count(String userId, String userNameEk, String yhlx, String userName, String zch, String qymc,
			String yhsf, Date start, Date end, String djjg, String realName,String cxmd, String bz) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;

		//sql = "SELECT count(*) FROM da_q_log a WHERE 1=1 ";
		sql = "select count(*) from (select row_number()over(partition by userid,username,nbxh,djid order by cxrq desc) as no,"
				+ "userid,username,realname,nbxh,zch,qymc,yhlx,cxrq,djid from da_q_log a WHERE 1=1 ";
		if ((djjg != null) && (!"".equals(djjg))) {
			sql = sql + " and exists (select 1 from da_q_ek_user b where a.userid=b.userid and b.unit='" + djjg + "') ";
		}
		if ((userId != null) && (userId.length() > 0)) {
			sql = sql + " AND userid LIKE ?";
		}
		if ((userNameEk != null) && (userNameEk.length() > 0)) {
			sql = sql + " AND username_EK LIKE ? ";
		}
		if ((yhlx != null) && (yhlx.length() > 0)) {
			sql = sql + " AND yhlx LIKE ? ";
		}
		if ((userName != null) && (userName.length() > 0)) {
			sql = sql + " AND username LIKE ? ";
		}
		if ((zch != null) && (zch.length() > 0)) {
			sql = sql + " AND zch LIKE ? ";
		}
		if ((qymc != null) && (qymc.length() > 0)) {
			sql = sql + " AND qymc LIKE ? ";
		}
		if ((yhsf != null) && (yhsf.length() > 0)) {
			sql = sql + " AND yhsf LIKE ? ";
		}
		if ((realName != null) && (realName.length() > 0)) {
			sql = sql + " AND realname LIKE ? ";
		}
		if ((cxmd != null) && (cxmd.length() > 0)) {
			sql = sql + " AND cxmd = ? ";
		}
		if (start != null) {
			sql = sql + " AND cxrq >= ? ";
		}
		if (end != null) {
			sql = sql + " AND cxrq <= ? ";
		}
		if ((bz != null) && (bz.length() > 0)) {
			sql = sql + " AND bz LIKE ? ";
		}
		sql = sql+") t where t.no=1 ";
		logger.debug(sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if ((userId != null) && (userId.length() > 0)) {
				pstmt.setString(i++, "%" + userId + "%");
			}
			if ((userNameEk != null) && (userNameEk.length() > 0)) {
				pstmt.setString(i++, "%" + userNameEk + "%");
			}
			if ((yhlx != null) && (yhlx.length() > 0)) {
				pstmt.setString(i++, "%" + yhlx + "%");
			}
			if ((userName != null) && (userName.length() > 0)) {
				pstmt.setString(i++, "%" + userName + "%");
			}
			if ((zch != null) && (zch.length() > 0)) {
				pstmt.setString(i++, "%" + zch + "%");
			}
			if ((qymc != null) && (qymc.length() > 0)) {
				pstmt.setString(i++, "%" + qymc + "%");
			}
			if ((yhsf != null) && (yhsf.length() > 0)) {
				pstmt.setString(i++, "%" + yhsf + "%");
			}
			if ((realName != null) && (realName.length() > 0)) {
				pstmt.setString(i++, "%" + realName + "%");
			}
			if ((cxmd != null) && (cxmd.length() > 0)) {
				pstmt.setString(i++, cxmd);
			}
			if (start != null) {
				pstmt.setTimestamp(i++, new Timestamp(start.getTime()));
			}
			if (end != null) {
				Calendar cd = Calendar.getInstance();
				cd.setTime(end);
				cd.add(5, 1);
				pstmt.setTimestamp(i++, new Timestamp(cd.getTimeInMillis()));
			}
			if ((bz != null) && (bz.length() > 0)) {
				pstmt.setString(i++, "%" + bz + "%");
			}
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}
		DbClose.freePreparedStatement(pstmt);
		DbClose.freeConnection(conn);
		DbClose.freeResultSet(rs);

		return 0;
	}

	public static QLog load(String logId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "SELECT * FROM da_q_log WHERE logid = ? ";

		logger.debug(sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			pstmt.setString(i++, logId);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rsToBean(rs);
			}
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}
		DbClose.freePreparedStatement(pstmt);
		DbClose.freeConnection(conn);
		DbClose.freeResultSet(rs);

		return null;
	}

	static QLog rsToBean(ResultSet rs) throws SQLException {
		QLog bean = new QLog();
		bean.setCxrq(rs.getTimestamp("cxrq"));
		bean.setLogId(rs.getString("logid"));
		bean.setNbxh(rs.getString("nbxh"));
		bean.setQymc(rs.getString("qymc"));
		bean.setUserId(rs.getString("userid"));
		bean.setUserName(rs.getString("username"));
		bean.setUserNameEk(rs.getString("username_EK"));
		bean.setYhlx(rs.getString("yhlx"));
		bean.setZch(rs.getString("zch"));
		bean.setRealName(rs.getString("realname"));
		bean.setYhsf(rs.getString("yhsf"));
		bean.setDyfs(rs.getString("dyfs"));
		//xuning add 20150124
		bean.setBz(rs.getString("bz"));
		bean.setCxmd(rs.getString("cxmd"));
		bean.setDjid(rs.getString("djid"));
		bean.setDays(rs.getInt("days"));
		bean.setSfdy(rs.getString("sfdy"));
		bean.setDyrq(rs.getTimestamp("dyrq"));
		return bean;
	}
	
	static QLog rsToBean1(ResultSet rs) throws SQLException {
		QLog bean = new QLog();
		bean.setCxrq(rs.getTimestamp("cxrq"));
		bean.setLogId(rs.getString("logid"));
		bean.setNbxh(rs.getString("nbxh"));
		bean.setQymc(rs.getString("qymc"));
		bean.setUserId(rs.getString("userid"));
		bean.setUserName(rs.getString("username"));
		bean.setUserNameEk(rs.getString("username_EK"));
		bean.setYhlx(rs.getString("yhlx"));
		bean.setZch(rs.getString("zch"));
		bean.setRealName(rs.getString("realname"));
		bean.setYhsf(rs.getString("yhsf"));
		bean.setDyfs(rs.getString("dyfs"));
		//xuning add 20150124
		bean.setBz(rs.getString("bz"));
		bean.setCxmd(rs.getString("cxmd"));
		bean.setDjid(rs.getString("djid"));
		bean.setDays(rs.getInt("days"));
		bean.setSfdy(rs.getString("sfdy"));
		bean.setDyrq(rs.getTimestamp("dyrq"));
		bean.setDyys(rs.getInt("dyys"));
		return bean;
	}
	
	static QPrintLog rsToBean2(ResultSet rs) throws SQLException {
		QPrintLog bean = new QPrintLog();
		bean.setLogId(rs.getString("logid"));
		bean.setNbxh(rs.getString("nbxh"));
		//bean.setQymc(rs.getString("qymc"));
		bean.setUserId(rs.getString("userid"));
		bean.setDjid(rs.getString("djid"));
		bean.setDays(rs.getInt("days"));
		bean.setSfdy(rs.getString("sfdy"));
		bean.setDyrq(rs.getTimestamp("dyrq"));
		bean.setInmlmc(rs.getString("inmlmc"));
		bean.setJnmlmc(rs.getString("jnmlmc"));
		return bean;
	}

	public static boolean insertEx(String logId, String userId, String fullName, String nm, String userName,
			String nbxh, String zch, String qymc, String bz) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "INSERT INTO da_q_log (logid,userid,username_EK,yhlx,username,cxrq,nbxh,zch,qymc,bz) VALUES (?,?,?,?,?,?,?,?,?,?)";
		logger.debug(sql);
		Date dt = new Date();
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			pstmt.setString(i++, logId);
			pstmt.setString(i++, userId);
			pstmt.setString(i++, fullName);
			pstmt.setString(i++, nm);
			pstmt.setString(i++, userName);

			pstmt.setTimestamp(i++, new Timestamp(dt.getTime()));

			pstmt.setString(i++, nbxh);
			pstmt.setString(i++, zch);
			pstmt.setString(i++, qymc);
			pstmt.setString(i++, bz);
			pstmt.execute();
			return true;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}
		DbClose.freePreparedStatement(pstmt);
		DbClose.freeConnection(conn);

		return false;
	}

	public static int dyfscount(String userId, String userNameEk, String yhlx, String userName, String zch,
			String qymc, String yhsf, Date start, Date end, String djjg, String realName,String cxmd) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;

		sql = "SELECT sum(dyfs) FROM da_q_log a WHERE 1=1 ";
		if ((djjg != null) && (!"".equals(djjg))) {
			sql = sql + " and exists (select 1 from da_q_ek_user b where a.userid=b.userid and b.unit='" + djjg + "') ";
		}
		if ((userId != null) && (userId.length() > 0)) {
			sql = sql + " AND userid LIKE ?";
		}
		if ((userNameEk != null) && (userNameEk.length() > 0)) {
			sql = sql + " AND username_EK LIKE ? ";
		}
		if ((yhlx != null) && (yhlx.length() > 0)) {
			sql = sql + " AND yhlx LIKE ? ";
		}
		if ((userName != null) && (userName.length() > 0)) {
			sql = sql + " AND username LIKE ? ";
		}
		if ((zch != null) && (zch.length() > 0)) {
			sql = sql + " AND zch LIKE ? ";
		}
		if ((qymc != null) && (qymc.length() > 0)) {
			sql = sql + " AND qymc LIKE ? ";
		}
		if ((yhsf != null) && (yhsf.length() > 0)) {
			sql = sql + " AND yhsf LIKE ? ";
		}
		if ((realName != null) && (realName.length() > 0)) {
			sql = sql + " AND realname LIKE ? ";
		}
		if ((cxmd != null) && (cxmd.length() > 0)) {
			sql = sql + " AND realname = ? ";
		}
		if (start != null) {
			sql = sql + " AND cxrq >= ? ";
		}
		if (end != null) {
			sql = sql + " AND cxrq <= ? ";
		}
		logger.debug(sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if ((userId != null) && (userId.length() > 0)) {
				pstmt.setString(i++, "%" + userId + "%");
			}
			if ((userNameEk != null) && (userNameEk.length() > 0)) {
				pstmt.setString(i++, "%" + userNameEk + "%");
			}
			if ((yhlx != null) && (yhlx.length() > 0)) {
				pstmt.setString(i++, "%" + yhlx + "%");
			}
			if ((userName != null) && (userName.length() > 0)) {
				pstmt.setString(i++, "%" + userName + "%");
			}
			if ((zch != null) && (zch.length() > 0)) {
				pstmt.setString(i++, "%" + zch + "%");
			}
			if ((qymc != null) && (qymc.length() > 0)) {
				pstmt.setString(i++, "%" + qymc + "%");
			}
			if ((yhsf != null) && (yhsf.length() > 0)) {
				pstmt.setString(i++, "%" + yhsf + "%");
			}
			if ((realName != null) && (realName.length() > 0)) {
				pstmt.setString(i++, "%" + realName + "%");
			}
			if ((cxmd != null) && (cxmd.length() > 0)) {
				pstmt.setString(i++, cxmd);
			}
			if (start != null) {
				pstmt.setTimestamp(i++, new Timestamp(start.getTime()));
			}
			if (end != null) {
				Calendar cd = Calendar.getInstance();
				cd.setTime(end);
				cd.add(5, 1);
				pstmt.setTimestamp(i++, new Timestamp(cd.getTimeInMillis()));
			}
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}

		return 0;
	}

	public static QLog query(String userId, String nbxh, String djid) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;

		QLog qlog = null;
		sql = "SELECT * FROM da_q_log a WHERE userid=? and nbxh=? and djid=? ";
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			pstmt.setString(i++, userId);
			pstmt.setString(i++, nbxh);
			pstmt.setString(i++, djid);
			
			rs = pstmt.executeQuery();
			if (rs.next()) {
				qlog = rsToBean(rs);
				return qlog;
			}
		} catch (SQLException e) {
			logger.info("db error sql:"+sql, e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}

		return null;
	}

	public static void updateByDy(QLog qLog) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ConnectDB connsyb = null;

		sql = "update da_q_log a set sfdy=?,dyrq=? WHERE userid=? and nbxh=? and djid=? ";
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			pstmt.setString(i++, qLog.getSfdy());
			pstmt.setTimestamp(i++, new Timestamp(qLog.getDyrq().getTime()));
			pstmt.setString(i++, qLog.getUserId());
			pstmt.setString(i++, qLog.getNbxh());
			pstmt.setString(i++, qLog.getDjid());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			logger.info("db error sql:"+sql, e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}
	}
	
	public static List loadDyLogList(String djid,String nbxh, Date start, Date end) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		List list = new ArrayList();
		//sql = "SELECT * FROM da_q_printlog a WHERE a.djid=? and a.nbxh=? ";
//		sql = "SELECT a.*,b.qymc,c.jnmlmc FROM da_q_printlog a,da_q_log b,da_qyzfjzmlb c "
//				+ "WHERE a.djid=b.djid and a.nbxh=b.nbxh and a.nbxh=c.nbxh and a.jnmlbh=c.jnmlbh "
//				+ "and a.djid=? and a.nbxh=? ";
		sql = "SELECT a.*,b.jnmlmc FROM da_q_printlog a,DA_QYZFJZMLB b "
				+ "WHERE a.djid=? and a.nbxh=? and a.jnmlbh=b.jnmlbh and dyrq >= ? and dyrq <= ? and a.nbxh=b.nbxh order by dyrq desc";
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			pstmt.setString(i++, djid);
			pstmt.setString(i++, nbxh);
			pstmt.setTimestamp(i++, new Timestamp(start.getTime()));
			Calendar cd = Calendar.getInstance();
			cd.setTime(end);
			cd.add(5, 1);
			pstmt.setTimestamp(i++, new Timestamp(cd.getTimeInMillis()));
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				list.add(rsToBean2(rs));
			}
		} catch (SQLException e) {
			logger.info("db error sql:"+sql, e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}

		return list;
	}
}
